#!/bin/env python

# comment : MySQL template for zabbix
# author  : sixther.me
# date    : 2014-08-12

import sys
import os

def get_item_value(key_word):
        mysql_status_data_df=open('/etc/zabbix/.mysql_status.txt')
        for i in mysql_status_data_df:
                items=i.strip().split()
                if items[0] == key_word:
                        return float(items[1])
def get_slave_status_value(key_word):
        slave_status_data_df=open('/etc/zabbix/.slave_status.txt')
        for i in slave_status_data_df:
                items=i.strip().split(':')
                if items[0] == key_word:
                        return items[1]
def get_slave_io_running():
        if get_slave_status_value('Slave_IO_Running').strip() == 'Yes':
                return 1
        else:
                return 0
def get_slave_sql_running():
        if get_slave_status_value('Slave_SQL_Running').strip() == 'Yes':
                return 1
        else:
                return 0
def get_seconds_behind_master():
        if get_slave_status_value('Seconds_Behind_Master').strip() == 'NULL':
                return 0
        else:
                return int(get_slave_status_value('Seconds_Behind_Master'))
def get_TPS():
        TPS=get_item_value('Com_commit') + get_item_value('Com_rollback')
        return TPS
def get_binlog_cache_hit_ratio():
        if get_item_value('Binlog_cache_use') == 0:
                return 0
        else:
                Binlog_Cache_Hit_Ratio=float((get_item_value('Binlog_cache_use') - get_item_value('Binlog_cache_disk_use')) / get_item_value('Binlog_cache_use')) * 100
                return Binlog_Cache_Hit_Ratio
def get_thread_cached_ratio():
        Thread_Cached_Ratio=float(1- get_item_value('Threads_created') / get_item_value('Connections')) * 100
        return Thread_Cached_Ratio
def get_innodb_buffer_read_ratio():
        Innodb_Buffer_Read_Ratio=float(1- get_item_value('Innodb_buffer_pool_reads') / get_item_value('Innodb_buffer_pool_read_requests')) * 100
        return Innodb_Buffer_Read_Ratio
def get_key_buffer_ratio():
        Key_Buffer_Ratio=float(1- get_item_value('Key_reads') / get_item_value('Key_write_requests')) * 100
        return Key_Buffer_Ratio
if __name__ == '__main__':
        status_data_array={
                'tps' : 'get_TPS()',
                'qps' : 'get_item_value(\'Queries\')',
                'binlog_cache_hit_ratio' : 'get_binlog_cache_hit_ratio()',
                'thread_cached_ratio' : 'get_thread_cached_ratio()',
                'innodb_buffer_read_ratio' : 'get_innodb_buffer_read_ratio()',
                'key_buffer_ratio' : 'get_key_buffer_ratio()',
                'created_tmp_disk_tables' : 'get_item_value(\'Created_tmp_disk_tables\')',
                'created_tmp_files' : 'get_item_value(\'Created_tmp_files\')',
                'created_tmp_tables' : 'get_item_value(\'Created_tmp_tables\')', 
                'select_full_join' : 'get_item_value(\'Select_full_join\')',
                'select_full_range_join' : 'get_item_value(\'Select_full_range_join\')',
                'select_range' : 'get_item_value(\'Select_range\')',
                'select_range_check' : 'get_item_value(\'Select_range_check\')',
                'select_scan' : 'get_item_value(\'Select_scan\')',
                'max_used_connections' : 'get_item_value(\'Threads_connected\')',
                'handler_read_first' : 'get_item_value(\'Handler_read_first\')',
                'handler_read_key' : 'get_item_value(\'Handler_read_key\')',
                'handler_read_next' : 'get_item_value(\'Handler_read_next\')',
                'handler_read_prev' : 'get_item_value(\'Handler_read_prev\')',
                'handler_read_rnd' : 'get_item_value(\'Handler_read_rnd\')',
                'handler_read_rnd_next' : 'get_item_value(\'Handler_read_rnd_next\')',
                'table_locks_immediate' : 'get_item_value(\'Table_locks_immediate\')',
                'table_locks_waited' : 'get_item_value(\'Table_locks_waited\')',
                'innodb_row_lock_current_waits' : 'get_item_value(\'Innodb_row_lock_current_waits\')',
                'innodb_row_lock_time' : 'get_item_value(\'Innodb_row_lock_time\')',
                'innodb_row_lock_time_avg' : 'get_item_value(\'Innodb_row_lock_time_avg\')',
                'innodb_row_lock_time_max' : 'get_item_value(\'Innodb_row_lock_time_max\')',
                'innodb_row_lock_waits' : 'get_item_value(\'Innodb_row_lock_waits\')',
                'slave_io_running' : 'get_slave_io_running()',
                'slave_sql_running' : 'get_slave_sql_running()',
                'seconds_behind_master' : 'get_seconds_behind_master()'
        }
        print "%.2f" % eval(status_data_array[sys.argv[1]])
